package com.css.fxfzxczh.modules.disasterSituationDataManage.samPoiData.repository.impl;

import com.css.fxfzxczh.constants.YNEnum;
import com.css.fxfzxczh.modules.disasterSituationDataManage.samPoiData.entity.SamPoiData;
import com.css.fxfzxczh.modules.disasterSituationDataManage.samPoiData.queryParams.SamPoiDataQuaParams;
import com.css.fxfzxczh.modules.disasterSituationDataManage.samPoiData.repository.SamPoiDataRepository;
import com.css.fxfzxczh.modules.disasterSituationDataManage.samPoiData.vo.ResultVo;
import com.css.fxfzxczh.util.PlatformObjectUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class SamPoiDataRepositoryImpl implements SamPoiDataRepository {

    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public Map<String, Object> getPage(SamPoiDataQuaParams queParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from xczh_sampling_point_data");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        if (!PlatformObjectUtils.isEmpty(queParams.getSamplingPointName())) {
            whereSql.append(" and sampling_point_name like '%" + queParams.getSamplingPointName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getSamplingPointCode())) {
            whereSql.append(" and sampling_point_code like '%" + queParams.getSamplingPointCode() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCreateUserName())) {
            whereSql.append(" and create_user_name like '%" + queParams.getCreateUserName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getXsemId())) {
            whereSql.append(" and xsem_id = '" + queParams.getXsemId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getType())) {
            whereSql.append(" and type = '" + queParams.getType() + "'");
        }

        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamPoiData.class)).size();
        List<SamPoiData> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(SamPoiData.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public boolean save(SamPoiData samPoiData) {
        /*String sql="insert into xczh_sampling_point_data(id,xsem_id,province,city,area,address,longitude,latitude,sampling_point_code," +
                "sampling_point_name,investigator,investigation_time,create_user,create_time,update_user,update_time,del_flag,geom,xstm_id) " +
                "values(:id, :xsemId, :province, :city, :area, :address, :longitude, :latitude, :samplingPointCode, :samplingPointName," +
                ":investigator, :investigationTime, :createUser, :createTime,:updateUser, :updateTime, :delFlag, :geom, :xstmId)";*/
        String sql="insert into xczh_sampling_point_data(id,xsem_id,province,city,area,address,longitude,latitude,sampling_point_code," +
                "sampling_point_name,create_user_name,create_user,create_time,update_user,update_time,del_flag,xstm_id,geom,type,xitm_id) " +
                "values(:id, :xsemId, :province, :city, :area, :address, :longitude, :latitude, :samplingPointCode, :samplingPointName," +
                ":createUserName,:createUser, :createTime,:updateUser, :updateTime, :delFlag, :xstmId,ST_GeomFromText(:geom,4490),:type,:xitmId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(samPoiData);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public List<SamPoiData> getSamPoiDataList(SamPoiDataQuaParams queParams) {
        StringBuilder sql = new StringBuilder("select * from xczh_sampling_point_data");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");

        if (!PlatformObjectUtils.isEmpty(queParams.getSamplingPointName())) {
            whereSql.append(" and sampling_point_name like '%" + queParams.getSamplingPointName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getSamplingPointCode())) {
            whereSql.append(" and sampling_point_code like '%" + queParams.getSamplingPointCode() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCreateUserName())) {
            whereSql.append(" and create_user_name like '%" + queParams.getCreateUserName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getXsemId())) {
            whereSql.append(" and xsem_id = '" + queParams.getXsemId() + "'");
        }
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamPoiData.class)).size();
        List<SamPoiData> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SamPoiData.class));
        return list;
    }

    @Override
    public String getMaxNum(String str) {
        String sql="select max(sampling_point_code) from xczh_sampling_point_data where del_flag = '0' and sampling_point_code like '%"+str+"%'";
        String maxNum=jdbcTemplate.queryForObject(sql,String.class);
        return maxNum;
    }

    @Override
    public SamPoiData findById(String id) {
        String sql="select  *,ST_AsText(geom) geom from xczh_sampling_point_data  where id = '"+id+"'";
        return jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(SamPoiData.class));
    }

    @Override
    public List<SamPoiData> getSamPoiList(String xsemId) {
        String sql = "select * from xczh_sampling_point_data where del_flag='0' and xsem_id = '"+xsemId+"'";
        List<SamPoiData> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SamPoiData.class));
        return list;
    }

    @Override
    public void update(SamPoiData samPoiData) {
        String sql = "update xczh_sampling_point_data set province = :province, city = :city, area = :area, address = :address, " +
                "longitude= :longitude, latitude = :latitude, sampling_point_name = :samplingPointName, " +
                "update_user = :updateUser ,update_time = :updateTime,geom = ST_GeomFromText(:geom,4490) where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(samPoiData);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void delete(String id) {
        String sql = "update xczh_sampling_point_data set del_flag= ? where id = ?";
        jdbcTemplate.update(sql, YNEnum.Y.toString(), id);
    }

    @Override
    public List<SamPoiData> getSamPoiListByXstmId(String xstmId) {
        String sql = "select id,sampling_point_name from xczh_sampling_point_data where del_flag='0' and xstm_id = '"+xstmId+"'";
        List<SamPoiData> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SamPoiData.class));
        return list;
    }

    @Override
    public Map<String, Object> getSamPoiByXsemId(String xsemId, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select a.id as samId,a.sampling_point_code,a.sampling_point_name,a.create_user_name ,a.create_time " +
                "from xczh_sampling_point_data a left join xczh_investigation_task_manage b on a.xitm_id=b.xitm_id ");
        StringBuilder whereSql = new StringBuilder(" where a.del_flag = '0' and b.del_flag = '0' and  a.xsem_id = '" + xsemId + "'");
        StringBuilder orderSql = new StringBuilder(" order by a.create_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        sql.append(whereSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ResultVo.class)).size();
        List<ResultVo> list = jdbcTemplate.query(sql.append(orderSql).append(limitSql).toString(), new BeanPropertyRowMapper<>(ResultVo.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public List<SamPoiData> getAllData(SamPoiDataQuaParams queParams) {
        StringBuilder sql = new StringBuilder("select *,ST_AsText(geom) geom from xczh_sampling_point_data");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0'");
        if (!PlatformObjectUtils.isEmpty(queParams.getSamplingPointName())) {
            whereSql.append(" and sampling_point_name like '%" + queParams.getSamplingPointName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getSamplingPointCode())) {
            whereSql.append(" and sampling_point_code like '%" + queParams.getSamplingPointCode() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCreateUserName())) {
            whereSql.append(" and create_user_name like '%" + queParams.getCreateUserName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getXsemId())) {
            whereSql.append(" and xsem_id = '" + queParams.getXsemId() + "'");
        }
//        if (!PlatformObjectUtils.isEmpty(queParams.getType())) {
//            whereSql.append(" and type = '" + queParams.getType() + "'");
//        }
        List<SamPoiData> list = jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(SamPoiData.class));
        return list;
    }

    @Override
    public void updateXsemId(String xsemId, String convertedXsemId) {
        String sql = "update xczh_sampling_point_data set xsem_id = '"+xsemId+"'  where xsem_id = '"+convertedXsemId+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public List<SamPoiData> findBySamplingPointName(String samplingPointName,String xsemId) {
        String sql="select  id,sampling_point_name,type from xczh_sampling_point_data  where sampling_point_name = '"+samplingPointName+"' and xsem_id='"+xsemId+"'";
        List<SamPoiData> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SamPoiData.class));
        return list;
    }

    @Override
    public Integer countByXsemId(String xsemId) {
        String sql ="select count(*) from xczh_sampling_point_data where del_flag='0' and xsem_id='"+xsemId+"'";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    @Override
    public List<SamPoiData> getUnassociatedData(String xsemId) {
        String sql="select  id,ST_AsText(geom) geom from xczh_sampling_point_data  where  del_flag='0' and xsem_id='"+xsemId+"'" +
                "and  (xstm_id is null or xstm_id='')";
        List<SamPoiData> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SamPoiData.class));
        return list;
    }

    @Override
    public void associatedSubarea(List<String> xspdIdList,String xstmId) {
        String sql = "update  xczh_sampling_point_data set xstm_id = :xstmId where id in (:ids)";
        Map idMap = new HashMap();
        idMap.put("ids", xspdIdList);
        idMap.put("xstmId", xstmId);
        namedParameterJdbcTemplate.update(sql,idMap);
    }

    @Override
    public List<SamPoiData> getSamListByType(String type) {
        String sql="select b.* from xczh_survey_task_manage a left join xczh_sampling_point_data b on a.xstm_id=b.xstm_id " +
                "where a.del_flag='0' and b.del_flag='0' and a.type='"+type+"'";
        List<SamPoiData> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SamPoiData.class));
        return list;
    }

    @Override
    public Boolean judPointPosition(String xstmId, BigDecimal longitude, BigDecimal latitude) {
        String sql = "SELECT ST_Contains((SELECT geom FROM xczh_survey_task_manage WHERE xstm_id = '"+xstmId+"'), " +
                "st_geometryfromtext('POINT("+longitude+" "+latitude+")',4490));";
        String value = jdbcTemplate.queryForObject(sql, String.class);
        return value.equals("t") ? true:false;
    }

    @Override
    public void updateAssociationbyXstmId(String id) {
        String sql = "update xczh_sampling_point_data set xstm_id= ? where xstm_id = ?";
        jdbcTemplate.update(sql,"", id);
    }
}
